

/***************************************************************************
* Name:			spFdsNCleanupObsoleteProtocol
* Purpose:		Delete all records from all protocols and related tables for
*				a protocol that has been deleted
*
*				
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE dbo.spFdsNCleanupObsoleteProtocol
(
	@prtProtocolId	int
)
AS
BEGIN

	SET NOCOUNT ON

	DECLARE	@_errorValue		int
	DECLARE	@_svcEntityId		int
	DECLARE	@_fwkResourceId		int
	DECLARE	@_annContainerId	int
	DECLARE	@_reviewContainerId	int
	
	BEGIN TRANSACTION spFdsNCleanupObsoleteProtocol

	-- get various IDs for this protocol
	SELECT	@_svcEntityId		= SvcEntityId,
			@_fwkResourceId		= FwkResourceId,
			@_annContainerId	= AnnAnnotationContainerId,
			@_reviewContainerId	= RevReviewContainerId
	FROM	prtProtocol
	WHERE	Id = @prtProtocolId

	

	
	/***** Review Data *****/

	-- get Reviews for the protocols itself
	SELECT		Id AS RevReviewId
	INTO		#_tmpRevReviewIDs_to_Delete
	FROM		RevReview
	WHERE		RevReviewContainerId = @_reviewContainerId

	
	--  get all the related RevBoardReviews 
	SELECT		Id AS RevBoardReviewId
	INTO		#_tmpRevBoardReviewIDs_to_Delete
	FROM		RevBoardReview
	INNER JOIN	#_tmpRevReviewIDs_to_Delete
		ON			RevBoardReview.RevReviewId = #_tmpRevReviewIDs_to_Delete.RevReviewId

	--  get all the related RevBoardReviewDetails 
	SELECT		Id AS RevBoardReviewDetailId
	INTO		#_tmpRevBoardReviewDetailIDs_to_Delete
	FROM		RevBoardReviewDetail
	INNER JOIN	#_tmpRevBoardReviewIDs_to_Delete
		ON			RevBoardReviewDetail.RevBoardReviewId =#_tmpRevBoardReviewIDs_to_Delete.RevBoardReviewId
	

	
	--1  delete RevRexBoardReviewDetail
	DELETE		RevRexBoardReviewDetail
	FROM		RevRexBoardReviewDetail
	INNER JOIN	#_tmpRevBoardReviewDetailIDs_to_Delete
		ON			RevRexBoardReviewDetail.RevBoardReviewDetailId =
					 #_tmpRevBoardReviewDetailIDs_to_Delete.RevBoardReviewDetailId

	--2 delete the RevReviewNotifications
	DELETE		RevReviewNotification
	FROM		RevReviewNotification
	INNER JOIN	#_tmpRevBoardReviewDetailIDs_to_Delete
		ON			RevReviewNotification.RevBoardReviewDetailId =
					 #_tmpRevBoardReviewDetailIDs_to_Delete.RevBoardReviewDetailId

	--3 delete the RevReviewMeeting
	DELETE		RevReviewMeeting
	FROM		RevReviewMeeting
	INNER JOIN	#_tmpRevBoardReviewDetailIDs_to_Delete
		ON			RevReviewMeeting.RevBoardReviewDetailId =
					 #_tmpRevBoardReviewDetailIDs_to_Delete.RevBoardReviewDetailId
	
	--4 delete the RevBoardReviewDetail
	DELETE		RevBoardReviewDetail
	FROM		RevBoardReviewDetail
	INNER JOIN	#_tmpRevBoardReviewDetailIDs_to_Delete
		ON			RevBoardReviewDetail.Id =
					 #_tmpRevBoardReviewDetailIDs_to_Delete.RevBoardReviewDetailId	
	
	--5 delete RevRexBoardReview
	DELETE		RevRexBoardReview
	FROM		RevRexBoardReview
	INNER JOIN	#_tmpRevBoardReviewIDs_to_Delete
		ON			RevRexBoardReview.Id =
					 #_tmpRevBoardReviewIDs_to_Delete.RevBoardReviewId
			
	
	--6 delete the RevBoardReview
	DELETE		RevBoardReview
	FROM		RevBoardReview
	INNER JOIN	#_tmpRevBoardReviewIDs_to_Delete
		ON			RevBoardReview.Id =
					#_tmpRevBoardReviewIDs_to_Delete.RevBoardReviewId

	--7 delete the RevReview 
	DELETE		RevReview
	FROM		RevReview
	INNER JOIN	#_tmpRevReviewIDs_to_Delete
		ON			RevReview.Id =
					#_tmpRevReviewIDs_to_Delete.RevReviewId

	-- Drop the review temporary tables 
	
	
	/**************************/
	/***** SvcEntity Data *****/


	-- protocol (i.e. entity) sponsors
	DELETE		PrtStaffedPerson
	FROM		PrtStaffedPerson
	INNER JOIN	SvcEntity_AdmStaffedPerson_Map
		ON			PrtStaffedPerson.SvcEntityStaffedPersonMapId = SvcEntity_AdmStaffedPerson_Map.Id
	WHERE		SvcEntity_AdmStaffedPerson_Map.SvcEntityId = @_svcEntityId

	-- entity-to-sponsor mappings
	DELETE		SvcEntity_AdmSponsor_Map
	WHERE		SvcEntityId = @_svcEntityId

	-- entity-to-staff mappings
	DELETE		SvcEntity_AdmStaffedPerson_Map
	WHERE		SvcEntityId = @_svcEntityId

	-- organizations
	DELETE		SvcEntity_FwkDomainOrganization_Map
	WHERE		SvcEntityId = @_svcEntityId

	-- contacts
	DELETE		SvcEntity_AdmContact_Map
	WHERE		SvcEntityId = @_svcEntityId

	/***** end SvcEntity Data *****/
	/******************************/


	

	/***** annotations prep *****/
	SELECT		Id AS AnnAnnotationId
	INTO		#_tmpAnnAnnotation
	FROM		AnnAnnotation
	WHERE		ParentContainerId = @_annContainerId

	-- framework resource mappings
	DELETE		FwkResource_DomainOrganization_Map
	WHERE		FwkResourceId = @_fwkResourceId
	
		

	DELETE		FwkResource_DomainPrincipal_Map
	WHERE		FwkResourceId = @_fwkResourceId
	
	--- Defunct table

	Delete	FwkAccessProvision
	From FwkResource_DmnPrincipal_Map fdm 
	INNER JOIN FwkAccessProvision fp 
	ON fdm.id = fp.PrivilegedPrincipalId
	WHERE	fdm.FwkResourceId = @_fwkResourceId 


	DELETE		FwkResource_DmnPrincipal_Map
	WHERE		FwkResourceId = @_fwkResourceId
	--- End defunct table

	/*********************************/
	-- annotations
	DELETE		AnnSystemAnnotation
	FROM		AnnSystemAnnotation
	INNER JOIN	#_tmpAnnAnnotation
		ON			AnnSystemAnnotation.AnnAnnotationId = #_tmpAnnAnnotation.AnnAnnotationId

	DELETE		AnnAnnotationAttribute
	FROM		AnnAnnotationAttribute
	INNER JOIN	#_tmpAnnAnnotation
		ON			AnnAnnotationAttribute.AnnAnnotationId = #_tmpAnnAnnotation.AnnAnnotationId

	DELETE		AnnUserAnnotation
	FROM		AnnUserAnnotation
	INNER JOIN	#_tmpAnnAnnotation
		ON			AnnUserAnnotation.AnnAnnotationId = #_tmpAnnAnnotation.AnnAnnotationId

	DELETE		AnnNote
	FROM		AnnNote
	INNER JOIN	#_tmpAnnAnnotation
		ON			AnnNote.AnnAnnotationId = #_tmpAnnAnnotation.AnnAnnotationId

	DELETE		AnnTask
	FROM		AnnTask
	INNER JOIN	#_tmpAnnAnnotation
		ON			AnnTask.AnnAnnotationId = #_tmpAnnAnnotation.AnnAnnotationId

	DELETE		AnnAnnotation
	FROM		AnnAnnotation
	INNER JOIN	#_tmpAnnAnnotation
		ON			AnnAnnotation.Id = #_tmpAnnAnnotation.AnnAnnotationId
	
	/*
		Now we're ready to delete the agreement and all the residual pieces
	*/
	
	/*** Protocol foreign keys ***/
	DELETE PrtProtocol_DmnOrganization_Map
	where PrtProtocolId = @prtProtocolId

	
	DELETE PrtProtocolContact
	where PrtProtocolId = @prtProtocolId
	
	DELETE PrtProtocol_DmnUser_Map
	where PrtProtocolId = @prtProtocolId



	DELETE		HumHumanProtocol
	WHERE		PrtProtocolId = @prtProtocolId

	DELETE		PrtProtocol
	WHERE		Id = @prtProtocolId
	
	DELETE		SvcEntity
	WHERE		Id = @_svcEntityId
	
	DELETE		FwkResource
	WHERE		Id = @_fwkResourceId

	

	DELETE		RevReviewContainer
	WHERE		Id = @_reviewContainerId

	

	DELETE		AnnAnnotationContainer
	WHERE		Id = @_annContainerId

	DROP TABLE #_tmpAnnAnnotation
	DROP TABLE #_tmpRevReviewIDs_to_Delete
	DROP TABLE #_tmpRevBoardReviewIDs_to_Delete
	DROP TABLE #_tmpRevBoardReviewDetailIDs_to_Delete




	IF @@ERROR <> 0
 	BEGIN
		GOTO ERROR_HANDLER
 	END
			
	SET NOCOUNT OFF
	
	COMMIT TRANSACTION  spFdsNCleanupObsoleteProtocol
	
	return 1

ERROR_HANDLER: 
	SET NOCOUNT OFF
        ROLLBACK TRANSACTION spFdsNCleanupObsoleteProtocol
	return 0


END

GO
